﻿Imports System.Data.Linq
Imports System.Linq
Public Class MOilPrice
    Private db As New TanNamChinhDataContext
    Public Function getOilfeulList1() As Table(Of tblOilfuel)
        Try
            Return db.tblOilfuels
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function getOilfeulList2() As IQueryable(Of tblOilfuel)
        Try
            Dim query = From b In db.tblOilFuels
                        Order By b.DatePush Descending
                        Select b
            'Return (From r In db.tblOilFuels
            '           Select r).Single
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function getOilfeulList3() As ISingleResult(Of TNCDAL.GetPushOilListResult)
        Try
            Dim query = db.GetPushOilList()

            'From listoil In db.tblOilFuels()
            '  Select New With {listoil.ID_Oilfuel,
            ' listoil.DatePush,
            ' listoil.Liter,
            ' listoil.UnitPrice,
            '.ThanhTien = listoil.Liter * listoil.UnitPrice,
            '  listoil.ID_Truck,
            '  listoil.ID_Driver,
            'listoil.ID_Sign}()   '
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function getOilfeulList3(ByVal month As Integer, ByVal year As Integer) As IQueryable
        Try
            Dim query = From b In db.tblOilFuels
                        Where b.DatePush.Value.Month = month And b.DatePush.Value.Year = year
                        Select New With {b.ID_Oilfuel,
                                         b.DatePush,
                                           b.Liter,
                                           b.UnitPrice,
                                                      .ThanhTien = b.Liter * b.UnitPrice,
                                           b.ID_Truck,
                                           b.ID_Driver,
                                           b.ID_Sign}

            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function getOilfeulList4(ByVal month As Integer, ByVal year As Integer) As IQueryable
        Try
            Dim query = From b In db.tblOilFuels
                        Where b.DatePush.Value.Month = month And b.DatePush.Value.Year = year
                        Select New With {b.ID_Oilfuel,
                                         b.DatePush,
                                           b.Liter,
                                           b.UnitPrice,
                                           .ThanhTien = b.Liter * b.UnitPrice,
                                           .TruckNo = If(b.tblTruck IsNot Nothing, b.tblTruck.TruckNumber, String.Empty),
                                           .DriverName = If(b.tblDriver IsNot Nothing, b.tblDriver.DriverName, String.Empty),
                                           .Sign = If(b.tblDriver1 IsNot Nothing, b.tblDriver1.DriverName, String.Empty)}

            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    'Public Function getOilfeulList3() As ISingleResult(Of TNCDAL.GetPushOilListResult)

    'End Function
    Public Function getSumofLiter(ByVal month As Integer, ByVal year As Integer) As Integer
        Dim sums As Integer
        Try
            sums = (From p In db.tblOilFuels _
                    Where p.DatePush.Value.Month = month And p.DatePush.Value.Year = year
                    Select CInt(p.Liter.Value)).Sum()
        Catch ex As Exception
            Throw ex
        End Try
        Return sums
    End Function
    Public Function getSumofMoney(ByVal month As Integer, ByVal year As Integer) As Integer
        Dim sums As Integer
        Try
            sums = (From p In db.tblOilFuels _
                    Where p.DatePush.Value.Month = month And p.DatePush.Value.Year = year
                    Select (CInt(p.Liter.Value) * CInt(p.UnitPrice.Value))).Sum()
        Catch ex As Exception
            Throw ex
        End Try
        Return sums
    End Function

    Public Sub InsertNewRecord(ByVal obj As tblOilfuel)
        Try

            db.GetTable(Of tblOilfuel)().InsertOnSubmit(obj)
            db.SubmitChanges()
        Catch ex As Exception
            Throw ex
        End Try
        ' db.tblOilfuels.InsertOnSubmit(obj)
      
    End Sub
    Public Sub InsertTotalMonth(ByVal obj As tblTotalMonth)
        Try

            db.GetTable(Of tblTotalMonth)().InsertOnSubmit(obj)
            db.SubmitChanges()
        Catch ex As Exception
            Throw ex
        End Try
        ' db.tblOilfuels.InsertOnSubmit(obj)

    End Sub
    Public Sub UpdateTotalMonth(ByVal obj As tblTotalMonth)
        Dim getData = (From b In db.tblTotalMonths
                                Where b.Year = obj.Year And b.Month = obj.Month
                                Select b).Single()
        getData.TotalPayment = obj.TotalPayment
        getData.PreviousMonth = obj.PreviousMonth
        getData.Discount = obj.Discount
        getData.TotalLiter = obj.TotalLiter
        getData.TotalMoneyPush = obj.TotalMoneyPush
        getData.ExcessMonth = obj.ExcessMonth
        db.SubmitChanges()

    End Sub
    Public Sub UpdateRecord(ByVal obj As tblOilfuel)
        Dim getData = (From b In db.tblOilfuels
                                Where b.ID_Oilfuel = obj.ID_Oilfuel
                                Select b).Single()
        getData.UnitPrice = obj.UnitPrice
        getData.DatePush = obj.DatePush
        getData.ID_Truck = obj.ID_Truck
        getData.ID_Driver = obj.ID_Driver
        getData.ID_Sign = obj.ID_Sign
        getData.Liter = obj.Liter
        db.SubmitChanges()

    End Sub
    Public Sub DeleteRecord(ByVal id As Integer)
        Dim getData = (From b In db.tblOilfuels
                                Where b.ID_Oilfuel = id
                                Select b).Single()
        db.tblOilFuels.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    Public Sub DeleteRecordTotalMonth(ByVal id As Integer)
        Dim getData = (From b In db.tblTotalMonths
                                Where b.ID = id
                                Select b).Single()
        db.tblTotalMonths.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    Public Function getPaymentMonthList(ByVal month As Integer, ByVal year As Integer) As IQueryable(Of tblTotalMonth)
        Dim query = Nothing
        Try

            If month = 0 And year = 0 Then
                query = From b In db.tblTotalMonths
                        Order By b.Year Descending, b.Month Descending
                        Select b
                Return query
            ElseIf month = 0 Then
                '     Dim query = From b In db.tblTotalMonths
                '        Where b.Year = year
                'Select b.ID, b.Month, b.TotalPayment, b.PreviousMonth, b.Discount, b.TotalLiter, b.TotalMoneyPush, b.ExcessMonth
                'Order By Month Ascending
                query = From b In db.tblTotalMonths
                         Order By b.Year Descending, b.Month Descending
                  Where b.Year = year
                 Select b
            ElseIf month <> 0 Then
                'nếu có tháng nhập vào
                query = From b In db.tblTotalMonths
                        Where b.Year = year And b.Month = month
                        Order By b.Year Descending, b.Month Descending
                        Select b
            End If



            'Return (From r In db.tblCurrencies
            '           Select r).Single

        Catch ex As Exception
            Throw ex
        End Try
        Return query
    End Function
End Class
